package dao;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import bean.News;
import util.JDBCUtil;

import javax.servlet.http.HttpServletRequest;

/**
 * 
 * NewsDAO类：访问数据库以返回相关新闻信息
 *
 */
public class NewsDAO {
	private Connection conn = null;	//数据库连接
	private static final int PAGE_NEWS_SIZE = 7;	//一页中新闻的条数

	public NewsDAO() {

	}

	/**
	 * 
	 * @param news 新闻信息
	 *
	 *@return boolean 插入成功与否
	 */
	public boolean insertNews(News news) {
		PreparedStatement preStatement = null;

		try {
			this.conn = JDBCUtil.getConn();
			preStatement = this.conn.prepareStatement("insert news values(?, ?, ?, ?, ?, ?, ?,?)");
			preStatement.setInt(1, news.getId());
			preStatement.setString(2, news.getTitle());
			preStatement.setString(3, news.getPublisher());
			preStatement.setString(4, news.getTime());
			preStatement.setInt(5, news.getViews());
			preStatement.setString(6, news.getText());
			InputStream imgInputStream = news.getImg();
			if (news.getImg().available()>0) {
				preStatement.setBlob(7, news.getImg(), news.getImg().available());
				preStatement.setBoolean(8,true);

			} else {
				preStatement.setBlob(7, imgInputStream);
				preStatement.setBoolean(8,false);
			}
			int cnt = preStatement.executeUpdate();
			if (cnt > 0) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeStatement(preStatement);
			JDBCUtil.closeConn(this.conn);
		}
		return false;
	}

	/**
	 * 
	 * @param id 新闻编号
	 *
	 *@return boolean 删除成功与否
	 */
	public boolean deleteNews(int id) {
		PreparedStatement preStatement = null;

		try {
			this.conn = JDBCUtil.getConn();
			preStatement = this.conn.prepareStatement("delete from news where NewsID = ?");
			preStatement.setInt(1, id);
			int cnt = preStatement.executeUpdate();
			if (cnt > 0) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeStatement(preStatement);
			JDBCUtil.closeConn(this.conn);
		}
		return false;
	}

	/**
	 * 
	 * @param news 新闻信息
	 *
	 *@return boolean 更新成功与否
	 */
	public boolean updateNews(News news) {
		PreparedStatement preStatement = null;
		boolean success = true;

		try {
			this.conn = JDBCUtil.getConn();
			int id = news.getId();
			if (news.getTitle() != null) {
				success = updateTitle(this.conn, news.getTitle(), id);
			}
			if (success && (news.getText() != null)) {
				success = updateText(this.conn, news.getText(), id);
			}
			if (success && (news.getImg() != null)) {
				success = updateImg(this.conn, news.getImg(), id);
			}
			if (success && (news.getTime() != null)) {
				success = updateTime(this.conn, news.getTime(), id);
			}
			return success;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeStatement(preStatement);
			JDBCUtil.closeConn(this.conn);
		}
		return false;
	}

	/**
	 * 
	 * @param conn 数据库连接
	 * @param imgInputStream 图片流
	 * @param id 新闻编号
	 *
	 *@return boolean 更新图片成功与否
	 */
	public boolean updateImg(Connection conn, InputStream imgInputStream, int id) {
		PreparedStatement preStatement = null;

		try {
			preStatement = conn.prepareStatement("update news set NewsPhoto = ? where NewsID = ?");
			if (imgInputStream != null) {
				preStatement.setBlob(1, imgInputStream, imgInputStream.available());
			} else {
				preStatement.setBlob(1, imgInputStream);
			}
			preStatement.setInt(2, id);
			int cnt = preStatement.executeUpdate();
			if (cnt > 0) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeStatement(preStatement);
		}
		return false;
	}

	/**
	 * 
	 * @param conn 数据库连接
	 * @param text 正文
	 * @param id 新闻编号
	 *
	 *@return boolean 更新正文成功与否
	 */
	public boolean updateText(Connection conn, String text, int id) {
		PreparedStatement preStatement = null;

		try {
			preStatement = conn.prepareStatement("update news set NewsText = ? where NewsID = ?");
			preStatement.setString(1, text);
			preStatement.setInt(2, id);
			int cnt = preStatement.executeUpdate();
			if (cnt > 0) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeStatement(preStatement);
		}
		return false;
	}

	/**
	 * 
	 * @param conn 数据库连接
	 * @param time 发布时间
	 * @param id 新闻编号
	 *
	 *@return boolean 更新发布时间成功与否
	 */
	public boolean updateTime(Connection conn, String time, int id) {
		PreparedStatement preStatement = null;

		try {
			preStatement = conn.prepareStatement("update news set ReleaseTime = ? where NewsID = ?");
			preStatement.setString(1, time);
			preStatement.setInt(2, id);
			int cnt = preStatement.executeUpdate();
			if (cnt > 0) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeStatement(preStatement);
		}
		return false;
	}

	/**
	 * 
	 * @param conn 数据库连接
	 * @param title 新闻标题
	 * @param id 新闻编号
	 *
	 *@return boolean 更新新闻标题成功与否
	 */
	public boolean updateTitle(Connection conn, String title, int id) {
		PreparedStatement preStatement = null;

		try {
			preStatement = conn.prepareStatement("update news set NewsTitle = ? where NewsID = ?");
			preStatement.setString(1, title);
			preStatement.setInt(2, id);
			int cnt = preStatement.executeUpdate();
			if (cnt > 0) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeStatement(preStatement);
		}
		return false;
	}

	/**
	 * 
	 * @param page 页数
	 *
	 *@return List<News> 指定页的所有新闻
	 */
	public List<News> getCurrentPageNews(int page) {
		PreparedStatement preStatement = null;
		ResultSet res = null;
		int position = 0;
		int counter = 0;
		List<News> newsList = new ArrayList<News>();

		try {
			int totalRow = getAllRecords();
			int totalPage = getTotalPage(totalRow);
			position = (page - 1) * PAGE_NEWS_SIZE;
			if (page != totalPage) {
				counter = PAGE_NEWS_SIZE;
			} else {
				counter = Integer.MAX_VALUE;
			}
			this.conn = JDBCUtil.getConn();
			preStatement = this.conn.prepareStatement("select * from news order by ReleaseTime desc limit ?, ?");
			preStatement.setInt(1, position);
			preStatement.setInt(2, counter);
			res = preStatement.executeQuery();
			while (res.next()) {
				News news = new News();
				news.setId(res.getInt("NewsID"));
				news.setTitle(res.getString("NewsTitle"));
				news.setPublisher(res.getString("AmdimName"));
				news.setTime(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(res.getTimestamp("ReleaseTime")));
				news.setViews(res.getInt("Newsviews"));
				news.setText(res.getString("NewsText"));
				if (res.getBoolean("hasImg")) {
					InputStream newsPhoto = res.getBlob("NewsPhoto").getBinaryStream();
					news.setImg(newsPhoto);
					news.setHasImg(true);
				}else {
					news.setImg(null);
					news.setHasImg(false);
				}
				newsList.add(news);
			}
			return newsList;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeResultSet(res);
			JDBCUtil.closeStatement(preStatement);
			JDBCUtil.closeConn(this.conn);
		}
		return null;
	}

	/**
	 * 
	 *
	 *@return int 新闻总记录数
	 */
	public int getAllRecords() {
		PreparedStatement preStatement = null;
		ResultSet res = null;

		try {
			this.conn = JDBCUtil.getConn();
			preStatement = this.conn.prepareStatement("select count(*) 'cnt' from news");
			res = preStatement.executeQuery();
			res.next();
			return res.getInt("cnt");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeResultSet(res);
			JDBCUtil.closeStatement(preStatement);
			JDBCUtil.closeConn(this.conn);
		}
		return 0;
	}

	/**
	 * 
	 * @param totalRow 新闻总记录数
	 *
	 *@return int 总页数
	 */
	public int getTotalPage(int totalRow) {
		int totalPage = 0;

		if (totalRow % PAGE_NEWS_SIZE == 0) {
			totalPage = totalRow / PAGE_NEWS_SIZE;
		} else {
			totalPage = totalRow / PAGE_NEWS_SIZE + 1;
		}
		return totalPage;
	}

	/**
	 * 
	 * @param id 新闻编号
	 *
	 *@return InputStream 图片流
	 */
	public InputStream getImgInputStream(int id) {
		PreparedStatement preStatement = null;
		ResultSet res = null;

		try {
			this.conn = JDBCUtil.getConn();
			preStatement = this.conn.prepareStatement("select NewsPhoto from news where NewsID = ?");
			preStatement.setInt(1, id);
			res = preStatement.executeQuery();
			res.next();
			return res.getBlob("NewsPhoto").getBinaryStream();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeResultSet(res);
			JDBCUtil.closeStatement(preStatement);
			JDBCUtil.closeConn(this.conn);
		}
		return null;
	}

	/**
	 * 
	 * @param id 新闻编号
	 *
	 *@return boolean 浏览量增加成功与否
	 */
	public boolean increaseViews(int id) {
		PreparedStatement preStatement = null;

		try {
			this.conn = JDBCUtil.getConn();
			preStatement = this.conn.prepareStatement("update news set Newsviews = Newsviews + 1 where NewsID = ?");
			preStatement.setInt(1, id);
			int cnt = preStatement.executeUpdate();
			if (cnt > 0) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeStatement(preStatement);
			JDBCUtil.closeConn(this.conn);
		}
		return false;
	}
}
